﻿Imports System.Data.SQLite
Public Class frmTestDB

    Private Sub btn_createdb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_createdb.Click
        Dim toDo As New Todo()
        toDo.Task = "Đây là tast"
        toDo.IsPrivate = False
        toDo.Note = "Đây là note"
        toDo.save()
    End Sub

    Private Sub btn_createtable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_createtable.Click
        Dim f As New OpenFileDialog
        f.Filter = "SQLite 3 (*.db3)|*.db3|All Files|*.*"
        If f.ShowDialog() = DialogResult.OK Then
            Try
                Dim dbPath As String = f.FileName

                Dim connection As New SQLiteConnection("Data Source = " & dbPath & "; UseUTF16Encoding=True")

                connection.Open()

                Dim statement As SQLiteCommand = DirectCast(connection.CreateCommand(), SQLiteCommand)

                statement.CommandText = "PRAGMA encoding"

                Dim encoding As String = statement.ExecuteScalar().ToString()

                'Check with a breakpoint the value of the encoding it will be empty

                'in order to check if this command works then change the connection string to the following and check again the value

                ' SQLiteConnection connection = new SQLiteConnection("Data Source = " + dbPath); after it will appear UTF-8 (Remember to delete the DB file)

                Dim transaction As SQLiteTransaction = DirectCast(connection.BeginTransaction(), SQLiteTransaction)



                statement = DirectCast(connection.CreateCommand(), SQLiteCommand)

                statement.Transaction = transaction

                statement.CommandText = "CREATE TABLE MYTABLE (ID INT, NAME NVARCHAR(100))"

                statement.ExecuteNonQuery()

                statement = DirectCast(connection.CreateCommand(), SQLiteCommand)

                statement.Transaction = transaction

                'statement.CommandText = "INSERT INTO MYTABLE (ID, NAME) VALUES (1,'SضزذÌ²²QضسزسرUЏЏνЋPشνذЌЌοزضPQ₤QЏ₤صQPذμξξ')"

                'statement.ExecuteNonQuery()

                transaction.Commit()

                Dim result As New DataSet()

                statement = DirectCast(connection.CreateCommand(), SQLiteCommand)

                statement.CommandText = "SELECT ID, NAME FROM MYTABLE"

                Dim adapter As New SQLiteDataAdapter(statement)

                'It will crash here


                adapter.Fill(result)

            Catch ex As Exception

                Throw New Exception("It doesn't work", ex.InnerException)
            End Try

        End If
    End Sub

    Private Sub btn_insert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_insert.Click
        Dim f As New OpenFileDialog
        f.Filter = "SQLite 3 (*.db3)|*.db3|All Files|*.*"
        If f.ShowDialog() = DialogResult.OK Then
            Dim SQLconnect As New SQLite.SQLiteConnection()
            Dim SQLcommand As SQLiteCommand
            SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
            SQLconnect.Open()
            SQLcommand = SQLconnect.CreateCommand

            SQLcommand.CommandText = "INSERT INTO foo (title, description) VALUES (@title, 'SضزذÌ²²QضسزسرUЏЏνЋPشνذЌЌοزضPQ₤QЏ₤صQPذμξξ')"
            Dim dp As New SQLiteParameter()
            dp.DbType = DbType.AnsiString
            SQLcommand.Parameters.AddWithValue("@title", "đây là title")
            'SQLcommand.Parameters.AddWithValue("@description", "đây là des")
            SQLcommand.ExecuteNonQuery()
            SQLcommand.Dispose()

            SQLconnect.Close()
        End If
    End Sub

    Private Sub btn_readrecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_readrecord.Click
        Dim f As New OpenFileDialog
        f.Filter = "SQLite 3 (*.db3)|*.db3|All Files|*.*"
        If f.ShowDialog() = DialogResult.OK Then
            Dim SQLconnect As New SQLite.SQLiteConnection()
            Dim SQLcommand As SQLiteCommand
            SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
            SQLconnect.Open()
            SQLcommand = SQLconnect.CreateCommand
            With SQLcommand
                .CommandText = "SELECT * FROM foo"
                Dim SQLreader As SQLite.SQLiteDataReader = .ExecuteReader() 'recordset
                lst_records.Items.Clear()
                While SQLreader.Read()
                    lst_records.Items.Add(String.Format("ID = {0}, Title = {1}, Description = {1}", SQLreader(0), SQLreader(1), SQLreader(2)))
                    'Optionally use the variable names to identify columns
                    'De lst_records.Items.Add(String.Format("ID = {0}, Title = {1}, Description = {1}", SQLreader("id"), SQLreader("title"), SQLreader("description")))
                End While
                .Dispose()
            End With
            SQLconnect.Close()
        End If
    End Sub

    Private Sub btn_tables_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_tables.Click
        Dim toDo As New Todo()
        toDo.GetDataTable()
    End Sub

    Private Sub frmTestDB_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Dim timeSpane As Long = Time2Unix(New Date())
        'MessageBox.Show(timeSpane)

    End Sub
End Class